#coding:utf-8

import requests,re,xlwt,sys,xlrd,os,MySQLdb.cursors,time
stdi,stdo,stde=sys.stdin,sys.stdout,sys.stderr
reload(sys)
sys.setdefaultencoding('utf-8')

print '开始抓去数据...'
#heade
headers={'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8','Accept-Encoding':'gzip,deflate','Host':'shimo.im','User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 Safari/537.36 SE 2.X MetaSr 1.0','X-Requested-With':'XMLHttpRequest'}

#cookie
cookie_str='gr_user_id=9848b408-4a3b-42f3-bee8-be624acd6574; rememberMeToken=DLQxuMSsBosUTES3; userId=1087312; _csrf=vmO3EvIV17t8UA8z2-a0s-iz; shimo_sid=s%3A36kVvAYXZXpv70nNaGa6ZVtkErlByX3u.PB75OzMr1p4memmqLQ7RbdZzxE5xCDF%2Fmp3SXM1zdQY; _gat=1; CNZZDATA1253139981=1788528812-1470625776-https%253A%252F%252Fwww.baidu.com%252F%7C1470903090; gr_session_id_ef6b2b07b37f413b8c5e5d7b4bc88923=bf5c8402-f1af-4dae-a51e-b8dfcbe61d71; gr_cs1_bf5c8402-f1af-4dae-a51e-b8dfcbe61d71=user_id%3A1087312; _ga=GA1.2.988047707.1470627739'

#组织COOKID
cookie_list=cookie_str.split(';')
cookie={}
for items in cookie_list:
    item=items.split('=')
    cookie[item[0].strip()]=item[1]

#抓取链接
urls='https://shimo.im/spreadsheet/E4VOSRksta0rA4K8'

r=requests.get(urls,headers=headers,cookies=cookie)
datas=r.content

#解析数据
datas=re.findall('\"content\"\:\"(.*)\"\,\"crumb\"',datas)[0]
datas=re.findall('\{.*\}',datas)[0]
datas=datas.replace('\\\"','"')
datas=datas.replace('\\\\"',"\'")
#字符串解析成字典
datas=eval(datas)

#构建excel
workbook = xlwt.Workbook() #注意Workbook的开头W要大写
table = workbook.add_sheet('table',cell_overwrite_ok=True)

#定义写入函数
def write(temp_dict):
    for i in temp_dict:
        try:
            pass
            row=temp_dict[0]
            col=temp_dict[1]
            cont=temp_dict[2].decode('UTF-8')
            table.write(int(col)-1,int(row),cont)
        except Exception,e:
            pass
temp=''
temp_dict={}
zimu={'A':0,'B':1,'C':2,'D':3,'E':4,'F':5,'G':6,'H':7,'I':8,'J':9,'K':10,'L':11}
for index in datas:
    pass
    list=[]
    temp_index=index
    _map=zimu.get(temp_index[0])
    if _map==None:
        continue
    list.append(_map)
    list.append(temp_index[1:])
    try:
        temp=datas[index][1][0][1]
        list.append(temp)
    except Exception,e:
        pass
    finally:
        temp_dict[index]=list

for i in temp_dict:
    if str(i).find('A')>-1:
        write(temp_dict[i])
    elif str(i).find('B')>-1:
        write(temp_dict[i])
    elif str(i).find('C')>-1:
        write(temp_dict[i])
    elif str(i).find('D')>-1:
        write(temp_dict[i])
    elif str(i).find('E')>-1:
        write(temp_dict[i])
    elif str(i).find('F')>-1:
        write(temp_dict[i])
    elif str(i).find('G')>-1:
        write(temp_dict[i])
    elif str(i).find('H')>-1:
        write(temp_dict[i])
    elif str(i).find('I')>-1:
        write(temp_dict[i])
    elif str(i).find('J')>-1:
        write(temp_dict[i])
    elif str(i).find('K')>-1:
        write(temp_dict[i])


workbook.save('json.xls')
if os.path.isfile(os.getcwd()+'/json.xls')==False:
    print '抓取数据失败,程序退出'
    exit()
print '抓去数据OK，开始往数据库中写入...'

data = xlrd.open_workbook('json.xls') # 打开xls文件
table = data.sheets()[0] # 打开第一张表
num_rows = table.nrows # 获取表的行数
#遍历sheet1中所有行row
conn = MySQLdb.connect("mysql.hfjy.red","mbi","hMWmOQuGHv","mbi",charset='utf8',cursorclass = MySQLdb.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute('truncate it_support_record')
conn.commit()
sql="insert into it_support_record(`lesson_plan_id`,`teacher_name`,`student_name`,`submit_time`,`problem_description`,`solved_person`,`solved_time`,`solved_process_description`,`solved_duration`,`if_solved`,`note`) values(%d,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

for curr_row in range(num_rows):
    row = table.row_values(curr_row)
    if curr_row==0:
        continue
    try:
        row[3]=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.mktime(time.strptime(str(row[3]),'%Y/%m/%d/%H:%M'))))
    except Exception,e:
        row[3]='0000-00-00 00:00:00'
    try:
        row[6]=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.mktime(time.strptime(str(row[6]),'%Y/%m/%d/%H:%M'))))
    except Exception,e:
        row[6]='0000-00-00 00:00:00'
    try:
        sql="insert into it_support_record(`lesson_plan_id`,`teacher_name`,`student_name`,`submit_time`,`problem_description`,`solved_person`,`solved_time`,`solved_process_description`,`solved_duration`,`if_solved`,`note`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql,row)
        conn.commit()
    except Exception,e:
        print str(e)+str(row[1])+'--执行失败'
cursor.close()
conn.close()
print '成功写入数据库！'
exit()
